Method and system for querying an on demand database service

ABSTRACT

Methods and systems are provided for querying a multi-tenant database. One exemplary method of generating an improved query plan to a database from an input query submitted to an on demand database service over a network involves determining guidance information appropriate to the input query based at least in part on an identity of a source of the input query, determining a database hint for processing the input query based at least in part on the guidance information, and providing the database hint to the database to form the improved query plan, thereby enabling the database to return an improved result responsive to the input query based at least in part upon the database hint.

CROSS-REFERENCE TO RELATED APPLICATION(S)

This application claims the benefit of U.S. provisional patentapplication Ser. No. 61/499,304, filed Jun. 21, 2011, the entire contentof which is incorporated by reference herein.

TECHNICAL FIELD

Embodiments of the subject matter described herein relate generally tocomputer systems and networks configured to support applicationsexecuting on behalf of users accessing them as services. Moreparticularly, embodiments of the subject matter relate to methods andsystems for efficiently querying a database service being provided in anon demand environment.

BACKGROUND

Modern software development is evolving away from the client-servermodel toward network-based processing systems that provide access todata and services via the Internet or other networks. In contrast totraditional systems that host networked applications on dedicated serverhardware, a “cloud” computing model allows applications to be providedover the network supplied by an infrastructure provider. Theinfrastructure provider typically abstracts the underlying hardware andother resources used to deliver a customer-developed application so thatthe customer no longer needs to operate and support dedicated serverhardware. The cloud computing model can often provide substantial costsavings to the customer over the life of the application because thecustomer no longer needs to provide dedicated network infrastructure,electrical and temperature controls, physical security and otherlogistics in support of dedicated server hardware.

Cloud-based architectures have been developed to improve collaboration,integration, and community-based cooperation between customer tenantswithout sacrificing data security. During operation, there are numeroussituations in which data and/or information needs to be retrieved (e.g.,for presentation to users) from a database being provided to customersin such a cloud-based environment. Conventional databases includemanagement software that determines what the database manufacturerconsiders to be an “optimal query plan” for executing the query andretrieving the desired set of data and/or information. However, themanufacturer's database management software is not always fully adaptedfor use in the cloud environment, so the “optimal query plan” generatedby such conventional systems does not reflect the nature of usersquerying the database, and therefore, may actually produce “optimal”query plans that are not in fact optimal when used in the cloudenvironment. What is really needed is a remedy to this and othershortcomings of the traditional database manager.

BRIEF DESCRIPTION OF THE DRAWINGS

A more complete understanding of the subject matter may be derived byreferring to the detailed description and claims when considered inconjunction with the following figures, wherein like reference numbersrefer to similar elements throughout the figures.

FIG. 1 is a block diagram of an exemplary system in which selectembodiments may be realized;

FIG. 2 is a block diagram of an exemplary querying system suitable foruse with the system of FIG. 1 in accordance with one or moreembodiments; and

FIG. 3 is a flow diagram of an exemplary querying process that may beperformed by the system of FIG. 1 and/or the querying system of FIG. 2in accordance with one or more exemplary embodiments.

DETAILED DESCRIPTION

Select embodiments may employ the techniques described hereinimplemented as one or a combination of methods, systems or processorexecuted code to form an improved query plan based at least in part upona query to a database received from a submitter in an on demandenvironment. In one example embodiment, guidance information may be usedto provide a “hint” to an on-demand database service's databasemanagement system in accordance with the improved query plan. Guidanceinformation may be determined at least in part on an identity of sourceof the input query, for example. As used herein, guidance informationmay take one or a combination of forms, such as without limitation, ametric, a value, integer, numeral, text, or the like, that is indicativeof or otherwise influenced by one or more entries, qualities orcharacteristics of a particular submitter in the on demand databaseservice. As will be described below with reference to specificembodiments, hints provided to a database management system may indicateto the database management system, and/or query optimizer, how at leasta portion of an improved query plan can be can be generated or otherwiseconstructed. Accordingly, exploiting such guidance information canthereby enable querying an on demand database service more efficiently.

While implementation specific differences exist, some embodiments mayemploy multi-tenancy techniques when providing the above describedbenefits, however multi-tenancy is not required by all embodiments.Generally speaking, multi-tenancy refers to a technique where a hardwareand software platform simultaneously supports multiple user groups (alsoreferred to as “organizations” or “tenants”) from a common computationalresource, such as a data storage element. An example type ofmulti-tenant data storage is a relational database (referred to as a“multi-tenant database”), however embodiments may be realized usingobject oriented and other types of databases as well. For example, a“tenant” or an “organization” can be used to refer to a group of one ormore users that shares access to common subset of the data within themulti-tenant database. In this regard, each tenant includes one or moreusers associated with, assigned to, or otherwise belonging to thatrespective tenant. Tenants may represent customers, customerdepartments, business or legal organizations, and/or any other entitiesthat maintain data for particular sets of users within a commonmulti-tenant system.

Although multiple tenants may share access to the server 102 and thedatabase 130, the particular data and services provided from the server102 to each tenant can be securely isolated from those provided to othertenants. The multi-tenant architecture therefore allows different setsof users to share functionality without necessarily sharing any of thedata 132 belonging to or otherwise associated with other tenants.Multi-tenant design choices can enable one or more advantages overconventional server virtualization systems. First, the multi-tenantplatform operator can often make improvements to the platform based uponcollective information from the entire tenant community. Additionally,because all users in the multi-tenant environment execute applicationswithin a common processing space, it is relatively easy to grant or denyaccess to specific sets of data for any user within the multi-tenantplatform, thereby improving collaboration and integration betweenapplications and the data managed by the various applications. Themulti-tenant architecture therefore allows convenient and cost effectivesharing of similar application features between multiple sets of users.

Turning now to FIG. 1, an exemplary system 100 suitably includes aserver 102 that dynamically creates and supports virtual applications128 based upon data 132 from a common database 130 such that it isshared. In some embodiments, database 130 may be shared among differenttenants, and in such case, it may be referred to as a multi-tenantdatabase; however, multi-tenancy is not a requirement of database 130.Data and services generated by the virtual applications 128 are providedvia a network 145 to any number of client devices 140, as desired. Eachvirtual application 128 is suitably generated at run-time using a commonapplication platform 110 that securely provides access to the data 132in the database 130 for each of the various submitters subscribing tothe system 100. In accordance with one non-limiting example, the system100 can implement one or more specific functions, such as for example acustomer relationship management (CRM) system, an Enterprise resourceplanning (ERP) system, a Partner Relationship Management (PRM) systemand the like.

The database 130 is any sort of repository or other data storage systemcapable of storing and managing the data 132 associated with any numberof submitters. The database 130 may be implemented using any type ofconventional database server hardware. In various embodiments, thedatabase 130 shares processing hardware 104 with the server 102. Inother embodiments, the database 130 is implemented using separatephysical and/or virtual database server hardware that communicates withthe server 102 to perform the various functions described herein. In anexemplary embodiment, the database 130 includes a database managementsystem or other equivalent software capable of determining a query planfor retrieving and providing a particular subset of the data 132 to aninstance of virtual application 128 in response to a query initiated orotherwise provided by a user of a client device 140, as described ingreater detail below.

In practice, the data 132 may be organized and formatted in any mannerto support the application platform 110. In various embodiments, thedata 132 is suitably organized into a relatively small number of largedata tables to maintain a semi-amorphous “heap”-type format. The data132 can then be organized as needed for a particular virtual application128. In various embodiments, conventional data relationships areestablished using any number of pivot tables 134 that establishindexing, uniqueness, relationships between entities, and/or otheraspects of conventional database organization as desired. Further datamanipulation and report formatting is generally performed at run-timeusing a variety of metadata constructs. Metadata within a universal datadirectory (UDD) 136, for example, can be used to describe any number offorms, reports, workflows, user access privileges, business logic andother constructs that are common Tenant-specific formatting, functionsand other constructs may be maintained as tenant-specific metadata 138for each tenant, as desired. Rather than forcing the data 132 into aninflexible global structure that is common to all tenants andapplications, the database 130 is organized to be relatively amorphous,with the pivot tables 134 and the metadata 138 providing additionalstructure on an as-needed basis. To that end, the application platform110 suitably uses the pivot tables 134 and/or the metadata 138 togenerate “virtual” components of the virtual applications 128 tologically obtain, process, and present the relatively amorphous data 132from the database 130.

The server 102 is implemented using one or more actual and/or virtualcomputing systems that collectively provide the dynamic applicationplatform 110 for generating the virtual applications 128. For example,the server 102 may be implemented using a cluster of actual and/orvirtual servers operating in conjunction with each other, typically inassociation with conventional network communications, clustermanagement, load balancing and other features as appropriate. The server102 operates with any sort of conventional processing hardware 104, suchas a processor 105, memory 106, input/output features 107 and the like.The input/output features 107 generally represent the interface(s) tonetworks (e.g., to the network 145, or any other local area, wide areaor other network), mass storage, display devices, data entry devicesand/or the like. The processor 105 may be implemented using any suitableprocessing system, such as one or more processors, controllers,microprocessors, microcontrollers, processing cores and/or othercomputing resources spread across any number of distributed orintegrated systems, including any number of “cloud-based” or othervirtual systems. The memory 106 represents any non-transitory short orlong term storage or other computer-readable media capable of storingprogramming instructions for execution on the processor 105, includingany sort of random access memory (RAM), read only memory (ROM), flashmemory, magnetic or optical mass storage, and/or the like. Thecomputer-executable programming instructions, when read and executed bythe server 102 and/or processor 105, cause the server 102 and/orprocessor 105 to establish, generate, or otherwise facilitate theapplication platform 110 and/or virtual applications 128 and performadditional tasks, operations, functions, and processes herein. It shouldbe noted that the memory 106 represents one suitable implementation ofsuch computer-readable media, and alternatively or additionally, theserver 102 could receive and cooperate with computer-readable media (notseparately shown) that is realized as a portable or mobile component orplatform, e.g., a portable hard drive, a USB flash drive, an opticaldisc, or the like.

The application platform 110 is any sort of software application orother data processing engine that generates the virtual applications 128that provide data and/or services to the client devices 140. In atypical embodiment, the application platform 110 gains access toprocessing resources, communications interfaces and other features ofthe processing hardware 104 using any sort of conventional orproprietary operating system 108. The virtual applications 128 aretypically generated at run-time in response to input received from theclient devices 140. For the illustrated embodiment, the applicationplatform 110 includes a bulk data processing engine 112, a querygenerator 114, a search engine 116 that provides text indexing and othersearch functionality, and a runtime application generator 120. Each ofthese features may be implemented as a separate process or other module,and many equivalent embodiments could include different and/oradditional features, components or other modules as desired.

The runtime application generator 120 dynamically builds and executesthe virtual applications 128 in response to specific requests receivedfrom the client devices 140. The virtual applications 128 are typicallyconstructed in accordance with the tenant-specific metadata 138, whichdescribes the particular tables, reports, interfaces and/or otherfeatures of the particular application 128. In various embodiments, eachvirtual application 128 generates dynamic web content that can be servedto a browser or other client program 142 associated with its clientdevice 140, as appropriate.

The runtime application generator 120 suitably interacts with the querygenerator 114 to efficiently obtain data 132 from the database 130 asneeded in response to input queries initiated or otherwise provided byusers of the client devices 140. In a typical embodiment, the querygenerator 114 considers the identity of the user requesting a particularfunction (as well as possibly the user's associated tenant in someimplementations), and then builds and executes queries to the database130 using system-wide metadata 136, user specific metadata 138, pivottables 134, and/or any other available resources. The query generator114 in this example therefore maintains security of the common database130 by ensuring that queries are consistent with access privilegesgranted to the user that initiated the request.

As will be described in greater detail below with reference to specificembodiments of FIGS. 2-3, in an exemplary embodiment employingmulti-tenancy, the query generator 114 maintains guidance informationcomprising database utilization statistics for each of the varioustenants supported by system 100 (e.g., tenant-specific databaseutilization statistics), and in response to a query initiated by a userassociated with a particular tenant, the query generator 114 utilizesthe database utilization statistics associated with that particulartenant to determine one or more database hints that are utilized by thedatabase 130 to determine an improved query plan for executing thequery.

Still referring to FIG. 1, the data processing engine 112 performs bulkprocessing operations on the data 132 such as uploads or downloads,updates, online transaction processing, and/or the like. In manyembodiments, less urgent bulk processing of the data 132 can bescheduled to occur as processing resources become available, therebygiving priority to more urgent data processing by the query generator114, the search engine 116, the virtual applications 128, etc.

In operation, developers may use the application platform 110 to createdata-driven virtual applications 128 for the customers that theysupport. Such virtual applications 128 may make use of interfacefeatures such as tenant-specific, for example, screens 124, universalscreens 122 or the like. Any number of tenant-specific and/or universalobjects 126 may also be available for integration into virtualapplications 128. The data 132 associated with each virtual application128 is provided to the database 130, as appropriate, and stored until itis requested or is otherwise needed, along with the metadata 138 thatdescribes the particular features (e.g., reports, tables, functions,etc.) of that particular virtual application 128. For example, a virtualapplication 128 may include a number of objects 126 accessible to usersof a particular tenant, for example, wherein for each object 126,information pertaining to its object type along with values for variousfields associated with that respective object type are maintained asmetadata 138 in the database 130. In this regard, the object type candefine the structure (e.g., the formatting, functions and otherconstructs) of each respective object 126 and the various fieldsassociated therewith.

Still referring to FIG. 1, the data and services provided by the server102 can be retrieved using any sort of personal computer, mobiletelephone, tablet or other network-enabled client device 140 on thenetwork 145. In an exemplary embodiment, the client device 140 includesa display device, such as a monitor, screen, or another conventionalelectronic display capable of graphically presenting data and/orinformation retrieved from the database 130, as will be described ingreater detail below. Typically, the user operates a conventionalbrowser or other client program 142 executed by the client device 140 tocontact the server 102 via the network 145 using a networking protocol,such as the hypertext transport protocol (HTTP) or the like. The usertypically authenticates his or her identity to the server 102 to obtaina session identifier (“SessionID”) that identifies the user insubsequent communications with the server 102. When the identified userrequests access to a virtual application 128, the runtime applicationgenerator 120 suitably creates the application at run time based uponthe metadata 138, as appropriate. As noted above, the virtualapplication 128 may contain Java, ActiveX, or other content that can bepresented using conventional client software running on the clientdevice 140; other embodiments may simply provide dynamic web or othercontent that can be presented and viewed by the user, as desired. Asdescribed in greater detail below, the query generator 114 suitablyobtains the requested subsets of data 132 from the database 130 asneeded to populate the tables, reports or other features of theparticular virtual application 128.

FIG. 2 illustrates an exemplary querying system 200 suitable for use ina computing system, such as the system 100 for example. One embodimentof the illustrated querying system 200 includes a server 202, which canbe one example embodiment of server 102 of FIG. 1, and a database 204,which can be one specific embodiment of database 130 of FIG. 1. Theserver 202 supports a virtual application 206, which can be one exampleembodiment of virtual application 128 of FIG. 1, and a query generator208, which can be one embodiment of query generator 114, that arecooperatively configured to retrieve data and/or information fromdatabase 204 and present or otherwise provide the retrieved data to auser of the virtual application 206, in one of a variety of techniques,examples of which will be described in greater detail below. While theelements in the querying system 200 of FIG. 2 correspond to elementsdescribed above in the context of system 100 of FIG. 1, and may sharecommon features and/or functionality, FIG. 2 does not represent the onlyembodiment of FIG. 1 contemplated herein.

As illustrated in FIG. 2, in an exemplary embodiment, the database 204maintains data, which can be one specific embodiment of data 132 of FIG.1, in at least one of a plurality of different tables 210. Any one table210 may comprise a set of data that includes a number of rowscorresponding to the number of entries in that respective table 210, anda number of columns across the rows that correspond to the differentpieces of data and/or information maintained in that respective table210. The database 204 includes a database management system 212 thatprovides an interface between the data maintained by the database 204(e.g., in tables 210 in one example embodiment) and the server 202and/or other external elements. The database management system 212embodies the software module of the database 204 that may be configuredto determine and/or otherwise maintain statistics for the database 204,such as, for example, the number of rows and/or columns of eachparticular table 210, the number of different distinct values for eachcolumn or each particular table 210, and schema information and/or otherrelational information for the one or more tables 210. The statisticsmaintained by the database management system 212 need not betenant-specific and need not reflect multi-tenancy of the various tables210.

In an exemplary embodiment, the database management system 212 receivesqueries from the query generator 208. As illustrated, the databasemanagement system 212 generates or otherwise supports a query engine 214that determines an improved query plan for performing the query. Thedatabase management system 212 then executes or otherwise performs thequery in accordance with the query plan determined by the query engine214 to retrieve the desired subset of the data maintained in the tables210 and/or database 204, and the database management system 212 providesthe retrieved data to the query generator 208 and/or virtual application206 as the result of the query.

In one example embodiment, employing multi-tenancy referenced in thecontext of FIG. 3, the query generator 208 can maintain databaseutilization statistics, for example, by tenant in a multi-tenantimplementation supported by the database 204. Guidance information isnot, however, limited to database utilization statistics nor limited tomulti-tenancy architectures, and may broadly include information aboutthe environment, users or arrangement of data in the database that couldreasonably assist in improving the query to the database. In anembodiment, the query generator 208 determines one or more databasehints for the query engine 214 based at least in part upon the inputquery generated by the virtual application 206 and the tenant-specificdatabase statistics for the tenant associated with the particularinstance of the virtual application 206. A query statement (e.g., astructured query language (SQL) statement or the like) can beconstructed to include or otherwise utilize the database hints. In thisregard, a database hint should be understood as an instruction or otherguidance provided to the query engine 214 that influences the improvedquery plan determined by the query engine 214. For example, in oneembodiment and without limitation, a database hint may be theidentification of a particular table of the plurality of tables 210 toquery first, a particular order for joining two or more tables 210 ofthe database 204 (e.g., a join order), a particular method for joiningtwo or more tables 210 of the database 204 (e.g., a join method or joinoperation), a particular access path for accessing and/or querying aparticular table 210 (e.g., a primary key index, a secondary key index,or the like), or combinations of these and other techniques. In thisregard, the tenant-specific database utilization statistics maintainedby the query generator 208 for the querying tenant provide a betterrepresentation of the querying tenant's data distribution in thedatabase 204 and/or the relationships between the querying tenant's datain the database 204, thereby allowing the query generator 208 toestimate or otherwise determine how to exploit the tenant-specificstatistics (which the database management system 212 is not aware of) toachieve the optimal (or lowest cost) query plan, as described in greaterdetail below.

FIG. 3 depicts an exemplary embodiment of a querying process 300suitable for implementation by one or more computing devices in acomputing system to obtain data and/or information from a database in anon demand database service. The various tasks performed in connectionwith the illustrated process 300 may be performed by software, hardware,firmware, or any combination thereof. For illustrative purposes, thefollowing description may refer back to elements mentioned above inconnection with FIGS. 1-2, however, this is not intended to limit theembodiments discussed with reference to FIGS. 1-2. In embodiments,portions of the querying process 300 may be performed by differentelements of the system 100 and/or the querying system 200, such as, forexample, the processor 105, the application platform 110, the dataprocessing engine 112, the query generator 208, a virtual application206, the database 204, the database management system 212, the queryengine 214, and/or a client device 140. It should be appreciated thatthe querying process 300 may include any number of additional oralternative tasks, the tasks need not be performed in the illustratedorder and/or the tasks may be performed concurrently, and/or the securequerying process 300 may be incorporated into embodiments, includingprocedure or process having additional functionality not limited to thatdescribed in detail herein. Moreover, one or more of the tasks shown anddescribed in the context of FIG. 3 could be omitted from a practicalembodiment of the querying process 300 without departing from thetechniques described herein.

Referring to FIG. 3, in an exemplary embodiment, the querying process300 begins by determining and maintaining database utilizationstatistics (task 302). Such database utilization statistics could bekept for each of the tenants supported by a multi-tenant database, forexample. Alternatively, database utilization statistics may be kept forall users of a single tenant database running in a cloud environment,one or more instances of a database image executing under virtualmachines in a networked cloud environment, or the like. In one exampleembodiment, the query generator 208 determines at least one utilizationstatistic for at least one of the tables 210 of the database 204 thatincludes an entry associated with (or belonging to) a respective tenant,for example. For example, in one embodiment, for each table 210including entries associated with a first tenant, the query generator208 may determine the number of rows (or entries) corresponding to thefirst tenant that exist within that respective table 210. It will beappreciated that such statistics are not limited to any particularutilization metric, and in practice, such statistics may additionallyand/or alternatively include the number of different distinct valuesamong the entries associated with that particular data in a particulartable 210, such as without limitation, a frequency of search bysubmitters, a time of last access by a submitter, a speed of retrievalof the underlying database system, any relational information indicativeof an association between the entries associated with that particularsubmitter of the query in one particular table 210 to one or more othertables 210 of the database 204, and/or other such schema information, orthe like. In accordance with one embodiment, the query generator 208determines statistics for each table 210 of the database 204 uponcreation of the table 210 and updates the statistics whenever entriesare added to and/or removed from a particular table 210. For example,each time a submitter utilizes the virtual application 206 to add and/ordelete entries from the database 204, the virtual application 206 maynotify the query generator 208 so that the query generator 208 mayupdate the statistics for that tenant to reflect the current state ofthe database 204. The statistics determined by the query generator 208are stored or otherwise persistently maintained, in memory 106 of FIG.1for example, to facilitate determining database hints, as will bedescribed in greater detail below.

Still referring to FIG. 3, the querying process 300 continues byreceiving or otherwise obtaining an input query or another request fordata and/or information from the database generated by a submitter of avirtual application (task 304). In an exemplary embodiment, the querygenerator 208 receives, from a virtual application 206, an input queryindicative of a request to retrieve a particular subset of data 132, forexample from the database 204 in response to an action by a submitter ofa client device 140, for example. For example, the virtual application206 may display one or more graphical user interface (GUI) elements inthe web browser 142 that are adapted to allow a non-automata submitterto view, modify, manipulate, or otherwise access data and/or informationin the database 204 that is available to that particular submitter, suchas the subset of the data 132 that the submitter is permitted to accessbased on the submitter's access privileges and the like. In response tothe submitter manipulating the GUI elements presented on the clientdevice 140 to select or otherwise indicate the set of data that thesubmitter would like the virtual application 206 to present (e.g., in areport, table, chart, graph, or other visual format), the virtualapplication 206 provides, to the query generator 208, an input queryindicative of or otherwise influenced by the GUI elements selected bythe submitter to retrieve a desired subset of data 132 needed to presentthe selected table, chart, graph or other visual report provided by thevirtual application 128, for example.

In an exemplary embodiment, in response to receiving the input query,the querying process 300 continues by identifying or otherwisedetermining the guidance information associated with the input query,such as utilization statistics of a particular submitter, or tenant, forexample, and determining one or more database hints based on the inputquery and the guidance information, for the associated with the querysubmitter or tenant or the like (tasks 306, 308). In accordance with oneembodiment, the virtual application 206 provides the query generator 208with identifiers that indicate the appropriate guidance information,such as for example and without limitation an identity of a tenantand/or the submitter, associated with the virtual application 206 thatgenerated the input query. The query generator 208 accesses or otherwiseutilizes those identifiers to determine the appropriate guidanceinformation (for example, the tenant associated with the submitter ofthe client device 140), and then obtains (for example, from memory 106)the guidance information corresponding to the query (for example, thedatabase utilization statistics for the various tables of the database204 that are associated with the query submitter).

After obtaining the statistics for the query, the query generator 208determines one or more database hints based on the input query and thedatabase utilization statistics for the query. In some embodiments, thedatabase hints may be tenant-specific, in that the same input query fromtwo different tenants may produce two different sets of database hintsbased on differences between the database utilization statistics for thetwo different tenants for example. In an exemplary embodiment, thedatabase hints can include access paths (e.g., tables to be queried,indexes to be utilized, and the like), join methods or operations (e.g.,the manner in which two or more tables should be combined for purposesof the query), join orders (e.g., the order in which two or more tablesshould be combined for purposes of the query), and/or the like. Forexample, based on the input query, the query generator 208 may initiallyidentify or otherwise determine the possible combination of indexesand/or columns of the various tables 210 maintained by the database 204to be queried. Based on the statistics associated with the query, thequery generator 208 may then determine which of the tables 210 and/orindexes to be queried first and/or which of the tables 210 of thedatabase 204 to be joined and/or combined to most likely obtain theresults of the input query in accordance with some criterion orcriteria, such as for example and without limitation, the lowest cost(e.g., least amount of time and/or computing resources required). To putit another way, based on the statistics for the query and the inputquery, the query generator 208 determines or otherwise identifies themost selective combinations of tables, indexes, access paths, joinoperations and/or join orders.

For example, the query generator 208 may determine that the input queryrequires data and/or information from a first column (Column 1) in afirst table (Table 1) of the database 204 that has 100,000 entries (orrows) along with data and/or information from a second column (Column 2)in a second table (Table 2) of the database 204 that has 20,000 entries(or rows). Based on the number of rows in each of the identified tablesthat are associated with the query, the query generator 208 maydetermine or otherwise identify one of the columns as the primary index(or key) for executing the query. For example, if there are 5,000entries applicable to the query in Table 1 and 10,000 entries applicableto the query in Table 2, the query generator 208 may determine that thedatabase management system 212 should begin performing the query onColumn 1 of Table 1 rather than Column 2 of Table 2, and thus, determinea database hint for the database management system 212 and/or queryengine 214 that indicates the query plan should begin with Column 1 ofTable 1 (or that Table 1 should proceed Table 2 in a particular joinoperation). In other situations, based on the statistics, the querygenerator 208 may determine that Column 2 of Table 2 is more selective(e.g., a greater number of distinct values across the entries applicableto the query) than Column 1 of Table 1 and determine that the databasemanagement system 212 should begin performing the query on Column 2 ofTable 2 rather than Column 1 of Table 1 (or that Table 2 should proceedTable 1 in a particular join operation).

Still referring to FIG. 3, after determining the database hint(s), thequerying process 300 continues by providing the one or more databasehints to the database management system along with the input query forsubsequent execution (task 310). In an exemplary embodiment, the querygenerator 208 constructs a query statement (e.g., in SQL or anothersuitable database querying language) based on the input query that alsoincludes the database hint(s) that indicate to the database managementsystem 212 and/or query engine 214 the access paths, join operations,join orders, tables and/or indexes of the database 204 that should beused by the database management system 212 when generating the queryplan. In response to receiving the database hints, the query engine 214generates or otherwise creates a plurality of possible query plans basedon the input query and the database hints and identifies the possiblequery plan having the best fit to an established criterion (criteria),such as for example and without limitation, lowest estimated cost (e.g.,based on required computing resources, amount of computing timerequired, and the like) as the improved query plan. In this manner, theimproved query plan is influenced by the database hints. In someembodiments, the improved query plan may be understood as beingtenant-specific, in that the same input query from two different tenantsmay produce two different improved query plans based on the differencesbetween the database hints statistics for the two tenants, for example.After the query engine 214 creates the improved query plan based on theinput query and the database hints, the database management system 212executes or otherwise performs the query using that improved query planto obtain the results of the input query from the database 204.

It should be noted that by virtue of the techniques described,embodiments can enable providing an improved query plan that can be moreefficient or otherwise have a lower cost than the “optimal” query planthat a conventional database management system would have otherwisechosen. For example, absent the techniques described herein, in a joinof Table 1 and Table 2, a conventional database management system mayotherwise choose a query plan that begins with Table 2 (e.g., by havingTable 2 precede Table 1 in a join order) based on the total number ofentries in Table 2 (20,000) relative to the total number of entries inTable 1 (100,000), when in fact, there are fewer entries in Table 1(e.g., 5,000 as compared to 10,000) that are applicable to the query, asdiscussed above. Thus, in contrast to such failings of conventionalapproaches, the improved query plans achieved by embodiments like thosedescribed herein may be more selective, more efficient, or otherwiseachieve lower cost by enabling consideration for database utilizationstatistics which are otherwise unknown by the conventional approaches.

Referring again to FIG. 3, in an exemplary embodiment, the queryingprocess 300 continues by providing or otherwise presenting the resultsof the query to the submitter responsible for generating the query (task312). In this regard, after executing the input query, the database 204(e.g., database management system 212) provides the query results to theinstance of the virtual application 206 that generated the input query,wherein the virtual application 206 may format, organize, or otherwisemodify the query results and display or otherwise present a graphicalrepresentation of at least a portion of the query results on the clientdevice 140 within the web browser 142. For example, a virtual CRMapplication 206 may construct a table, chart, graph, report, or anothergraphical representation of the data and/or information retrieved fromthe database 204 in accordance with the GUI elements selected by anon-machine submitter of the client device 140.

The foregoing description is merely illustrative in nature and is notintended to limit the embodiments of the subject matter or theapplication and uses of such embodiments. Furthermore, there is nointention to be bound by any expressed or implied theory presented inthe technical field, background, or the detailed description. As usedherein, the word “exemplary” means “serving as an example, instance, orillustration.” Any implementation described herein as exemplary is notnecessarily to be construed as preferred or advantageous over otherimplementations, and the exemplary embodiments described herein are notintended to limit the scope or applicability of the subject matter inany way.

For the sake of brevity, conventional techniques related to computerprogramming, computer networking, database querying, and otherfunctional aspects of the systems (and the individual operatingcomponents of the systems) may not be described in detail herein. Inaddition, those skilled in the art will appreciate that embodiments maybe practiced in conjunction with any number of system and/or networkarchitectures, data transmission protocols, and device configurations,and that the system described herein is merely one suitable example.Furthermore, certain terminology may be used herein for the purpose ofreference only, and thus is not intended to be limiting. For example,the terms “first”, “second” and other such numerical terms do not implya sequence or order unless clearly indicated by the context.

Embodiments of the subject matter may be described herein in terms offunctional and/or logical block components and with reference tosymbolic representations of operations, processing tasks, and functionsthat may be performed by various computing components or devices. Suchoperations, tasks, and functions are sometimes referred to as beingcomputer-executed, computerized, software-implemented, orcomputer-implemented. In this regard, it should be appreciated that thevarious block components shown in the figures may be realized by anynumber of hardware, software, and/or firmware components configured toperform the specified functions. For example, an embodiment of a systemor a component may employ various integrated circuit components, e.g.,memory elements, digital signal processing elements, logic elements,look-up tables, or the like, which may carry out a variety of functionsunder the control of one or more microprocessors or other controldevices. In this regard, the subject matter described herein can beimplemented in the context of any computer-implemented system and/or inconnection with two or more separate and distinct computer-implementedsystems that cooperate and communicate with one another. In exemplaryembodiments, the subject matter described herein can be implemented inconjunction with a virtual application, such as customer relationshipmanagement (CRM) in an on demand environment.

While at least one exemplary embodiment has been presented in theforegoing detailed description, it should be appreciated that a vastnumber of variations exist. It should also be appreciated that theexemplary embodiment or embodiments described herein are not intended tolimit the scope, applicability, or configuration of the claimed subjectmatter in any way. Rather, the foregoing detailed description willprovide those skilled in the art with a convenient road map forimplementing the described embodiment or embodiments. It should beunderstood that various changes can be made in the function andarrangement of elements without departing from the scope defined by theclaims, which includes known equivalents and foreseeable equivalents atthe time of filing this patent application.

1. A method of generating an improved query plan to a database from aninput query submitted to an on demand database service over a network,the method comprising: determining, based at least in part on anidentity of a source of the input query, guidance informationappropriate to the input query; determining at least one hint to thedatabase to process the input query based at least in part on theguidance information; and providing the at least one hint to thedatabase to form the improved query plan, thereby enabling the databaseto return an improved result responsive to the input query based atleast in part upon the at least one hint.
 2. The method of claim 1,further comprising: receiving query results from the database, the queryresults comprising the improved result, wherein the database obtains thequery results in accordance with the improved query plan; andgraphically presenting at least a portion of the query results on adisplay device.
 3. The method of claim 2, further comprising receivingthe input query from a virtual application being accessed by a clientdevice over the network, the client device including the display device,wherein: the client device is associated with a querying tenant;determining the guidance information comprises determining the guidanceinformation based at least in part on the identity of the queryingtenant; and graphically presenting the portion of the query resultscomprises displaying the portion of the query results within the virtualapplication.
 4. The method of claim 3, the virtual application executingon a server coupled to the network, wherein: the server receives theinput query, obtains one or more database utilization statistics for thequerying tenant, determines one or more tenant-specific database hintsfor the querying tenant based on the one or more database utilizationstatistics, and provides the one or more tenant-specific database hintsto the database along with the input query; and the database generatesthe improved query plan based on the one or more tenant-specificdatabase hints and the input query and obtains the query results byexecuting the input query in accordance with the improved query plan. 5.The method of claim 1, wherein determining the guidance informationcomprises determining, for a first table of the database, a first metricinfluenced by entries associated with the source of the input query inthe first table.
 6. The method of claim 5, wherein determining the firstmetric comprises determining a number of the entries associated with thesource in the first table or a number of distinct values across theentries associated with the source in the first table.
 7. The method ofclaim 5, wherein determining the at least one hint comprises determininga first database hint for the first table based at least in part on thefirst metric.
 8. The method of claim 7, wherein determining the firstdatabase hint comprises determining a join order for the first table; ajoin operation for the first table, an access path for the first table,or a key index for the first table.
 9. The method of claim 5, wherein:determining the guidance information further comprises determining, fora second table of the database, a second metric influenced by entriesassociated with the source of the input query in the second table; anddetermining the at least one hint comprises determining a join order forthe first table and the second table based at least in part on the firstand second metrics.
 10. A method of querying a database supporting aplurality of tenants in a multi-tenant system, the method comprising:maintaining, by a server, database utilization statistics for a firsttenant of the plurality of tenants; receiving, by the server, an inputquery associated with the first tenant; determining, by the server, oneor more hints for querying the database based at least in part on thedatabase utilization statistics for the first tenant; providing, by theserver, the one or more hints and the input query to the database;determining, by the database, an optimal query plan for the input querybased at least in part on the one or more hints; executing, by thedatabase, the input query using the optimal query plan to obtain queryresults; and providing, by the database, the query results to theserver.
 11. The method of claim 10, the input query being received froma virtual application executing on the server, the virtual applicationbeing accessed by a client device coupled to the server over a network,wherein the method further comprises displaying, by the server, at leasta portion of the query results within the virtual application.
 12. Themethod of claim 10, the database utilization statistics comprising afirst metric for a first table of the database, the first metric beinginfluenced by entries associated with the first tenant in the firsttable, wherein determining the one or more hints for querying thedatabase comprises determining an access path for the first table basedon the first metric.
 13. The method of claim 10, the databaseutilization statistics comprising a first metric for a first table ofthe database and a second metric for a second table of the database, thefirst metric being influenced by entries associated with the firsttenant in the first table and the second metric being influenced byentries associated with the first tenant in the second table, whereindetermining the one or more hints for querying the database comprisesdetermining a join order for the first and second tables based on thefirst and second metrics.
 14. A computing system comprising a processorand a memory, wherein the memory comprises computer-executableinstructions that, when executed by the processor, cause the computingsystem to: receive an input query from a source supported by a database;determine a database utilization statistic for the source in thedatabase; determine a database hint based on the database utilizationstatistic and the input query; and provide the database hint and theinput query to the database, wherein the database generates a query planinfluenced by the database hint.
 15. The computing system of claim 14,wherein: the database executes the input query using the query plan toobtain query results and provides the query results to the computingsystem; and the computer-executable instructions cause the computingsystem to: generate a virtual application provided to a client deviceover a network; and display at least a portion of the query results onthe client device within the virtual application.
 16. The computingsystem of claim 15, wherein the virtual application comprises a customerrelationship management application.
 17. The computing system of claim14, the source comprising a querying tenant of a plurality of tenantssupported by the database and the database utilization statisticcomprising a tenant-specific database utilization metric for thequerying tenant, wherein the computer-executable instructions cause thecomputing system to: determine the tenant-specific database utilizationmetric for the querying tenant by determining, for a first table of thedatabase, a first metric influenced by entries associated with thequerying tenant in the first table; and determine the database hintbased at least in part on the first metric.
 18. The computing system ofclaim 17, wherein the database hint is an access path for the firsttable.
 19. The computing system of claim 17, wherein thecomputer-executable instructions cause the computing system to:determine, for a second table of the database, a second metricinfluenced by entries associated with the querying tenant in the secondtable; and determine the database hint based at least in part on thefirst metric and the second metric.
 20. The computing system of claim19, wherein the database hint is a join order for the first table andthe second table.